﻿Public Class empleados

    Dim cadena As String = "Data Source=CESAR-PC\SQLSERVER;Initial Catalog=CAPCrepuestos;Integrated Security=True"
#Region "Variables"
    Enum termino
        aprobado
        rechazado
    End Enum
    Dim accion As estado = estado.insertar

    Enum estado
        insertar
        modificar
    End Enum
#End Region
#Region "Funciones genéricas"
    Private Sub cargarCombo(ByVal combo As ComboBox, ByRef datos As DataTable, ByVal pk As String, ByVal descripcion As String)

        combo.DataSource = datos
        combo.DisplayMember = descripcion
        combo.ValueMember = pk

    End Sub

    Private Function leo_tabla(ByVal nombreTabla As String) As Data.DataTable

        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion

        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM " & nombreTabla
        tabla.Load(cmd.ExecuteReader())

        conexion.Close()

        Return tabla

    End Function
#End Region
#Region "Load"

    Private Sub empleados_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cargarCombo(cmbEstado, leo_tabla("Estado"), "id", "descripcion")
        cargarCombo(cmbHorario, leo_tabla("Horario"), "id", "descripcion")
        cargarCombo(txtCargo, leo_tabla("cargo"), "id", "descrpcion")
        cargarCombo(cmbTipoDocumento, leo_tabla("documento_tipo"), "id", "descripcion")

        txtNombre.Enabled = False
        txtApellido.Enabled = False
        txtCargo.Enabled = False
        txtCUIL.Enabled = False
        txtFechaAlta.Enabled = False
        txtFechaNacimiento.Enabled = False
        txtTelefono.Enabled = False
        cmbEstado.Enabled = False
        cmbHorario.Enabled = False
        ckbJueves.Enabled = False
        ckbLunes.Enabled = False
        ckbMartes.Enabled = False
        ckbMiercoles.Enabled = False
        ckbSabado.Enabled = False
        ckbViernes.Enabled = False

        btnModificar.Enabled = False
    End Sub
    Private Sub empleados_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If (MsgBox("¿Desea salir?", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Atencion") = MsgBoxResult.Yes) Then
            e.Cancel = False
        Else
            e.Cancel = True
        End If
    End Sub

#End Region
#Region "Botones"

    Private Sub btnNuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNuevo.Click

        txtNombre.Enabled = True
        txtApellido.Enabled = True
        txtCargo.Enabled = True
        txtCUIL.Enabled = True
        txtFechaAlta.Enabled = True
        txtFechaNacimiento.Enabled = True

        txtFechaAlta.Text = Today

        txtTelefono.Enabled = True
        cmbEstado.Enabled = True
        cmbHorario.Enabled = True
        cmbTipoDocumento.Enabled = True
        ckbJueves.Enabled = True
        ckbLunes.Enabled = True
        ckbMartes.Enabled = True
        ckbMiercoles.Enabled = True
        ckbSabado.Enabled = True
        ckbViernes.Enabled = True

        btnBuscarDocumento.Enabled = False
        btnBuscarLegajo.Enabled = False
        btnModificar.Enabled = False

        txtLegajo.Enabled = False
        txtLegajo.Text = cargarMayor()

        Me.accion = estado.insertar
    End Sub


    Private Sub btnGuardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGuardar.Click
        If Me.accion = estado.insertar Then
            insertar()
        Else
            modificar()
        End If
    End Sub
    Private Sub btnBuscarLegajo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuscarLegajo.Click, btnEliminar.Click

        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM Empleados WHERE legajo = " & txtLegajo.Text
        tabla.Load(cmd.ExecuteReader())
        If tabla.Rows.Count = 1 Then
            Me.txtNombre.Text = tabla.Rows(0)("nombre")
            Me.txtApellido.Text = tabla.Rows(0)("apellido")
            Me.txtFechaNacimiento.Text = tabla.Rows(0)("fecha_nacimiento")
            Me.txtFechaAlta.Text = tabla.Rows(0)("fecha_alta")
            Me.cmbTipoDocumento.SelectedValue = tabla.Rows(0)("tipo_documento")
            Me.txtNumero.Text = tabla.Rows(0)("numero")
            Me.txtCUIL.Text = tabla.Rows(0)("CUIL")
            Me.txtTelefono.Text = tabla.Rows(0)("telefono")
            Me.txtCargo.Text = tabla.Rows(0)("cargo")
            Me.cmbHorario.SelectedValue = tabla.Rows(0)("horario")
            Me.cmbEstado.SelectedValue = tabla.Rows(0)("estado")
            Me.ckbLunes.Checked = tabla.Rows(0)("lunes")
            Me.ckbMartes.Checked = tabla.Rows(0)("martes")
            Me.ckbMiercoles.Checked = tabla.Rows(0)("miercoles")
            Me.ckbJueves.Checked = tabla.Rows(0)("jueves")
            Me.ckbViernes.Checked = tabla.Rows(0)("viernes")
            Me.ckbSabado.Checked = tabla.Rows(0)("sabado")
        Else
            MsgBox("No se encontro el empleado", MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Aviso")
        End If


        conexion.Close()

        btnModificar.Enabled = True

    End Sub

    Private Sub btnBuscarDocumento_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuscarDocumento.Click
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM empleados WHERE numero = " & txtNumero.Text

        tabla.Load(cmd.ExecuteReader())

        Me.txtNombre.Text = tabla.Rows(0)("nombre")
        Me.txtApellido.Text = tabla.Rows(0)("apellido")
        Me.txtFechaNacimiento.Text = tabla.Rows(0)("fecha_nacimiento")
        Me.txtFechaAlta.Text = tabla.Rows(0)("fecha_alta")
        Me.cmbTipoDocumento.SelectedValue = tabla.Rows(0)("tipo_documento")
        Me.txtLegajo.Text = tabla.Rows(0)("legajo")
        Me.txtCUIL.Text = tabla.Rows(0)("CUIL")
        Me.txtTelefono.Text = tabla.Rows(0)("telefono")
        Me.txtCargo.Text = tabla.Rows(0)("cargo")
        Me.cmbHorario.SelectedValue = tabla.Rows(0)("horario")
        Me.cmbEstado.SelectedValue = tabla.Rows(0)("estado")
        Me.ckbLunes.Checked = tabla.Rows(0)("lunes")
        Me.ckbMartes.Checked = tabla.Rows(0)("martes")
        Me.ckbMiercoles.Checked = tabla.Rows(0)("miercoles")
        Me.ckbJueves.Checked = tabla.Rows(0)("jueves")
        Me.ckbViernes.Checked = tabla.Rows(0)("viernes")
        Me.ckbSabado.Checked = tabla.Rows(0)("sabado")


        conexion.Close()

        btnModificar.Enabled = True

    End Sub

    Private Sub btnModificar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModificar.Click
        txtCargo.Enabled = True
        cmbEstado.Enabled = True
        cmbHorario.Enabled = True
        txtTelefono.Enabled = True
        ckbJueves.Enabled = True
        ckbLunes.Enabled = True
        ckbMartes.Enabled = True
        ckbMiercoles.Enabled = True
        ckbSabado.Enabled = True
        ckbViernes.Enabled = True

        btnNuevo.Enabled = True
        Me.accion = estado.modificar

    End Sub


    Private Sub btnEliminar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEliminar.Click
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim tabla As New Data.DataTable

        conexion.ConnectionString = cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "DELETE FROM empleados WHERE legajo = " & txtLegajo.Text
        cmd.ExecuteNonQuery()
        conexion.Close()
    End Sub

    Private Sub btnCancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelar.Click
        btnBuscarLegajo.Enabled = True
        btnBuscarDocumento.Enabled = True
        btnModificar.Enabled = True
        btnNuevo.Enabled = True

        Me.resetData()
    End Sub

    Private Sub btnSalir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSalir.Click
        Me.Close()

    End Sub
#End Region
#Region "Funciones"
    Function insertar() As termino
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim consulta As String = ""
        Dim lunes, martes, miercoles, jueves, viernes, sabado As Integer
        If validar_Datos() = True Then


            If ckbLunes.Checked = True Then
                lunes = 1
            Else : lunes = 0

            End If

            If ckbMartes.Checked = True Then
                martes = 1
            Else : martes = 0

            End If

            If ckbMiercoles.Checked = True Then
                miercoles = 1
            Else : miercoles = 0

            End If

            If ckbJueves.Checked = True Then
                jueves = 1
            Else : jueves = 0
            End If


            If ckbViernes.Checked = True Then
                viernes = 1
            Else : viernes = 0

            End If

            If ckbSabado.Checked = True Then
                sabado = 1
            Else : sabado = 0

            End If

            conexion.ConnectionString = cadena
            conexion.Open()


            consulta = "insert into Empleados (nombre,apellido,legajo,fecha_alta, fecha_nacimiento,telefono,tipo_documento,numero,CUIL,cargo,horario,estado,lunes,martes,miercoles,jueves,viernes,sabado)"
            consulta &= "values ('" & txtNombre.Text & "'"
            consulta &= ", '" & Me.txtApellido.Text & "'"
            consulta &= ", " & Me.txtLegajo.Text
            consulta &= ", '" & Me.txtFechaAlta.Text & "'"
            consulta &= ", '" & Me.txtFechaNacimiento.Text & "'"
            consulta &= ", '" & Me.txtTelefono.Text & "'"
            consulta &= ", " & Me.cmbTipoDocumento.SelectedValue
            consulta &= ", '" & Me.txtNumero.Text & "'"
            consulta &= ", '" & Me.txtCUIL.Text & "'"
            consulta &= ", '" & Me.txtCargo.Text & "'"
            consulta &= ", " & Me.cmbHorario.SelectedValue
            consulta &= ", " & Me.cmbEstado.SelectedValue
            consulta &= ", " & lunes
            consulta &= ", " & martes
            consulta &= ", " & miercoles
            consulta &= ", " & jueves
            consulta &= ", " & viernes
            consulta &= ", " & sabado & ")"


            cmd.CommandType = CommandType.Text
            cmd.CommandText = consulta
            cmd.Connection = conexion
            cmd.ExecuteNonQuery()
            conexion.Close()

            MsgBox("El empleado fue guardado exitosamente", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "ATENCION")
        End If
        Return termino.aprobado
    End Function

    Private Function modificar() As termino
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim modificacion As String = ""
        Dim lunes, martes, miercoles, jueves, viernes, sabado As Integer
        If ckbLunes.Checked = True Then
            lunes = 1
        Else : lunes = 0

        End If

        If ckbMartes.Checked = True Then
            martes = 1
        Else : martes = 0

        End If

        If ckbMiercoles.Checked = True Then
            miercoles = 1
        Else : miercoles = 0

        End If

        If ckbJueves.Checked = True Then
            jueves = 1
        Else : jueves = 0
        End If


        If ckbViernes.Checked = True Then
            viernes = 1
        Else : viernes = 0

        End If

        If ckbSabado.Checked = True Then
            sabado = 1
        Else : sabado = 0

        End If

        conexion.ConnectionString = cadena
        conexion.Open()

        modificacion = "UPDATE empleados"
        modificacion &= " Set cargo='" & Me.txtCargo.Text & "'"
        modificacion &= ", estado=" & Me.cmbEstado.SelectedValue
        modificacion &= ", horario=" & Me.cmbHorario.SelectedValue
        modificacion &= ", telefono='" & Me.txtTelefono.Text & "'"
        modificacion &= ", lunes=" & lunes
        modificacion &= ", martes=" & martes
        modificacion &= ", miercoles=" & miercoles
        modificacion &= ", jueves=" & jueves
        modificacion &= ", viernes=" & viernes
        modificacion &= ", sabado=" & sabado
        modificacion &= " WHERE (legajo='" & Me.txtLegajo.Text & "')"

        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = modificacion

        cmd.ExecuteNonQuery()
        conexion.Close()

        Return termino.aprobado

    End Function

    Private Function cargarMayor() As Integer
        Dim conexion As New Data.SqlClient.SqlConnection
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim max As Integer

        conexion.ConnectionString = cadena
        conexion.Open()

        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT MAX(legajo) as Legajo FROM Empleados"
        max = cmd.ExecuteScalar
        conexion.Close()

        Return max + 1
    End Function

    Private Sub resetData()
        txtNombre.Enabled = False
        txtApellido.Enabled = False
        txtCargo.Enabled = False
        txtCUIL.Enabled = False
        txtFechaAlta.Enabled = False
        txtFechaNacimiento.Enabled = False
        txtTelefono.Enabled = False
        cmbEstado.Enabled = False
        cmbHorario.Enabled = False
        ckbJueves.Enabled = False
        ckbLunes.Enabled = False
        ckbMartes.Enabled = False
        ckbMiercoles.Enabled = False
        ckbSabado.Enabled = False
        ckbViernes.Enabled = False

        txtLegajo.Text = ""
        txtApellido.Text = ""
        txtCUIL.Text = ""
        txtFechaAlta.Text = ""
        txtFechaNacimiento.Text = ""
        txtNombre.Text = ""
        txtNumero.Text = ""
        txtTelefono.Text = ""
        cmbEstado.SelectedIndex = -1
        txtCargo.SelectedIndex = -1
        cmbHorario.SelectedIndex = -1
        cmbTipoDocumento.SelectedIndex = -1

        txtLegajo.Enabled = True
    End Sub
#End Region
#Region "Validaciones"

    Public Function validar_Datos() As Boolean

        If Me.txtApellido.Text = "" Then
            MessageBox.Show("Falta el Apellido", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Me.txtApellido.Focus()
            Return False
        End If

        If Me.txtNombre.Text = "" Then
            MessageBox.Show("Falta el Nombre", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Me.txtNombre.Focus()
            Return False
        End If

        If Me.cmbTipoDocumento.SelectedIndex = -1 Then
            MessageBox.Show("No ha seleccionado un tipo de documento valido", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Me.cmbTipoDocumento.Focus()
            Return False
        End If

        If Me.txtNumero.Text = "" Then
            MessageBox.Show("Falta el numero de documento", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Me.txtNumero.Focus()
            Return False
        End If

        If Me.txtCargo.Text = "" Then
            MessageBox.Show("Falta cargo del empleado", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Me.txtCargo.Focus()
            Return False
        End If

        If Me.cmbEstado.SelectedIndex = -1 Then
            MessageBox.Show("No ha seleccionado un estado civil", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Me.cmbEstado.Focus()
            Return False
        End If



        Return True

    End Function

    Private Sub txtNombre_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNombre.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack Then e.Handled = True
        txtNombre.MaxLength = 25
    End Sub

    Private Sub txtApellido_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtApellido.KeyPress
        If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> vbBack Then e.Handled = True
        txtApellido.MaxLength = 25
    End Sub


    Private Sub txtLegajo_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtLegajo.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack Then e.Handled = True
        txtLegajo.MaxLength = 6
    End Sub

    Private Sub txtFechaNacimiento_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtFechaNacimiento.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack Then e.Handled = True
    End Sub

    Private Sub txtNumero_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNumero.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack Then e.Handled = True
        txtNumero.MaxLength = 12
    End Sub

    Private Sub txtFechaAlta_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtFechaAlta.KeyPress
        If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> vbBack Then e.Handled = True
    End Sub

    Private Sub txtTelefono_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtTelefono.KeyPress
        If Not Char.IsDigit(e.KeyChar) Then e.Handled = True
        txtTelefono.MaxLength = 14
    End Sub
#End Region

   
End Class